/*
 * sysmo 数据库接口
 * Copyright (C) 2009  LiuCun
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */

package org.sysmo.db;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

/**
 * 数据库接口
 * @author LiuCun
 */
public class DatabaseInterface {

    private String jdbcPool;

    private DataSource dataSource = null;

    private Connection getConnection() throws SQLException {
        //return DriverManager.getConnection(url, user, password);
        try {
            if (dataSource == null) {
                    Context initCtx = new InitialContext();
                    Context envCtx = (Context) initCtx.lookup("java:comp/env");
                    dataSource = (DataSource) envCtx.lookup(jdbcPool);
            }
            return dataSource.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 构造函数
     * 
     * @throws java.lang.ClassNotFoundException 若数据库连接失败，则抛出此异常。
     * @throws java.lang.InstantiationException 若数据库连接失败，则抛出此异常。
     * @throws java.lang.IllegalAccessException 若数据库连接失败，则抛出此异常。
     * @throws java.sql.SQLException 若conn连接失败，则抛出此异常。
     */
    public DatabaseInterface(String jdbcPool)
            throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        /*this.url = "jdbc:mysql://localhost/se";
        this.user = "liucun";
        this.password = "se";*/
        this.jdbcPool = jdbcPool;
    }

    /**
     * 查询client_table中的纪录
     *
     * @param cid 对应于client_table中的cid。
     * @return 返回与给定cid相等的纪录。
     * @throws java.sql.SQLException 若查询失败，则抛出此异常。
     */
    public DbClient query_ClientTable(int cid) throws SQLException {
        Connection conn = getConnection();
        String sql = "select * from client_table where cid = "+cid;
       ResultSet rs = conn.createStatement().executeQuery(sql);
        try {
            if (rs.next()) {
                return (new DbClient(rs.getInt("cid"), rs.getString("name")));
           } else {
                return null;
            }
        } finally {
            if(conn != null)
               conn.close();
        }
    }

    /**
     * 获取所有客户端节点记录。
     *
     * @return 客户端节点列表。
     */
    public Vector<DbClient> query_ClientTable_all() throws SQLException {
        Vector<DbClient> client = new Vector();
        Connection conn = getConnection();
        String sql = "select * from client_table";
        ResultSet rs = conn.createStatement().executeQuery(sql);
        try {
            while (rs.next()) {
                client.add(new DbClient(rs.getInt("cid"), rs.getString("name")));
            }
        } finally {
            /*if(rs != null)
                rs.close();*/
            if (conn != null) {
                conn.close();
            }
        }
        return client;
    }

    /**
     * 删除client_table中的纪录
     * TODO: 多个statement，用完应该关掉!!!
     *
     * @param cid 表中cid的数值
     * @throws java.sql.SQLException 若删除失败，则抛出此异常。
     */
    public void delete_ClientTable(int cid) throws SQLException {
        Connection conn = getConnection();
        int i;
        String sql;
        ResultSet rs;
        Vector<DbId> result = new Vector();
        Vector<DbId> result1 = new Vector();
        String psql = "delete from client_table where cid = "+cid;
        PreparedStatement pstmt = conn.prepareStatement(psql);
        pstmt.executeUpdate();
         pstmt.close();

        psql = "delete from warning_table where cid = "+cid;
        pstmt = conn.prepareStatement(psql);
        pstmt.executeUpdate();
         pstmt.close();

        sql = "select * from item_table where cid = "+cid;
        rs = conn.createStatement().executeQuery(sql);
        while(rs.next()) {
           result.add(new DbId(rs.getInt("iid")));
        }

        psql = "delete from item_table where iid = ?";
        pstmt = conn.prepareStatement(psql);
        for(i = 0;i < result.size(); ++i) {
            pstmt.setInt(1, result.get(i).id);
            pstmt.executeUpdate();

        }
        pstmt.close();

        for(i = 0; i < result.size(); ++i) {
            sql = "select * from graph_table where iid = "+result.get(i).id;
            rs = conn.createStatement().executeQuery(sql);
            while(rs.next()) {
            result1.add(new DbId(rs.getInt("gid")));
            }
        }
        result.clear();

        psql = "delete from graph_table where gid = ?";
        pstmt = conn.prepareStatement(psql);
        for(i = 0;i < result1.size(); ++i) {
            pstmt.setInt(1, result1.get(i).id);
            pstmt.executeUpdate();
        }
        pstmt.close();

        for(i = 0; i < result1.size(); ++i) {
            sql = "select * from value_table where gid = "+result1.get(i).id;
            rs = conn.createStatement().executeQuery(sql);
            while(rs.next()) {
            result.add(new DbId(rs.getInt("vid")));
            }
        }
        result1.clear();

        psql = "delete from value_table where vid = ?";
        pstmt = conn.prepareStatement(psql);
        for(i = 0;i < result.size(); ++i) {
            pstmt.setInt(1, result.get(i).id);
            pstmt.executeUpdate();
        }
        pstmt.close();

        psql = "delete from data_table where vid = ?";
        pstmt = conn.prepareStatement(psql);
        for(i = 0;i < result.size(); ++i) {
            pstmt.setInt(1, result.get(i).id);
            pstmt.executeUpdate();
        }
        pstmt.close();

        /*if(rs != null)
            rs.close();
        if(pstmt != null)
            pstmt.close();*/
        if (conn != null) {
            conn.close();
        }
    }

    /**
     * 更新client_table中的纪录，若cid为0，则表示插入纪录。
     *
     * @param new_client 表中的一列
     */
    public void update_ClientTable(DbClient new_client) throws SQLException {
        Connection conn = getConnection();
        String psql;
        PreparedStatement pstmt;

        if(new_client.cid == 0) {
            psql = "insert into client_table values(null, ?)";
            pstmt = conn.prepareStatement(psql);
            pstmt.setString(1, new_client.name);
            pstmt.executeUpdate();
            ResultSet rs = pstmt.getGeneratedKeys();
            if (rs.next()) {
                new_client.cid = rs.getInt(1);
            }
            pstmt.close();
        }

        else {
            psql = "update client_table set name = ? where cid = ?";
            pstmt = conn.prepareStatement(psql);
            pstmt.setString(1, new_client.name);
            pstmt.setInt(2, new_client.cid);
            pstmt.executeUpdate();
            pstmt.close();
        }

        if(conn != null)
            conn.close();
    }

    /**
     * 查询item_table中的纪录
     *
     * @param iid 表中iid的值
     * @return 返回与iid相等的纪录。
     */
    public DbItem query_ItemTable(int iid) throws SQLException {
        Connection conn = getConnection();
        String sql = "select * from item_table where iid = "+iid;
        ResultSet rs = conn.createStatement().executeQuery(sql);
        try {
            if (rs.next()) {
                return (new DbItem(rs.getInt("iid"), rs.getInt("cid"), rs.getString("name"),
                        rs.getInt("time_interval"), rs.getString("mail")));
            } else {
                return null;
            }
        } finally {
            if(conn != null)
                conn.close();
        }
    }

    /**
     * 获取指定客户端下的所有监控项。
     *
     * @param clientID 指定客户端ID。
     * @return 监控项列表，若客户端不存在则返回空列表（非null）。
     */
    public Vector<DbItem> query_ItemTableOfClient(int clientID) throws SQLException {
        Vector<DbItem> item = new Vector();
        Connection conn = getConnection();
        String sql = "select * from item_table where cid = "+clientID;
        ResultSet rs = conn.createStatement().executeQuery(sql);
        try {
            while (rs.next()) {
                item.add(new DbItem(rs.getInt("iid"), rs.getInt("cid"), rs.getString("name"),
                                    rs.getInt("time_interval"), rs.getString("mail")));
            }
        } finally {
            /*if(rs != null)
                rs.close();*/
            if (conn != null) {
                conn.close();
            }
        }
        return item;
    }

    /**
     * 查询item_table的纪录
     *
     * @param name 监控项的名字
     * @param cid 监控项的父节点
     * @return 返回与name和cid都匹配的纪录
     */
    public DbItem query_ItemTable(String name, int cid) throws SQLException {
        Connection conn = getConnection();
        String psql;
        PreparedStatement pstmt;
        ResultSet rs;

        psql = "select * from item_table where name = ? && cid = ?";
        pstmt = conn.prepareStatement(psql);
        pstmt.setString(1, name);
        pstmt.setInt(2, cid);
        rs = pstmt.executeQuery();

        try {
            if (rs.next()) {
                return (new DbItem(rs.getInt("iid"), rs.getInt("cid"), rs.getString("name"),
                                    rs.getInt("time_interval"), rs.getString("mail")));
            } else {
                return null;
            }
        } finally {
            if(pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
    }

    /**
     * 删除delete_table中的指定条目
     *
     * @param iid 表中iid的值
     */
    public void delete_ItemTable(int iid) throws SQLException {
        Connection conn = getConnection();
        int i;
        String sql;
        ResultSet rs;
        Vector<DbId> result = new Vector();
        Vector<DbId> result1 = new Vector();
        String psql = "delete from item_table where iid = "+iid;
        PreparedStatement pstmt = conn.prepareStatement(psql);
        pstmt.executeUpdate();
        pstmt.close();

        psql = "delete from warning_table where iid = "+iid;
        pstmt = conn.prepareStatement(psql);
        pstmt.executeUpdate();
        pstmt.close();//!!!!用完应该关闭

        sql = "select * from graph_table where iid = "+iid;
        rs = conn.createStatement().executeQuery(sql);
        while(rs.next()) {
           result.add(new DbId(rs.getInt("gid")));
        }

        psql = "delete from graph_table where gid = ?";
        pstmt = conn.prepareStatement(psql);
        for(i = 0;i < result.size(); ++i) {
            pstmt.setInt(1, result.get(i).id);
            pstmt.executeUpdate();
        }
        pstmt.close();//!!!!用完应该关闭，特别是循环中

        for(i = 0; i < result.size(); ++i) {
            sql = "select * from value_table where gid = "+result.get(i).id;
            Statement stmt = conn.createStatement();//这里其实可以用PrepareStatement
            rs = stmt.executeQuery(sql);
            while(rs.next()) {
                result1.add(new DbId(rs.getInt("vid")));
            }
            stmt.close();//!!!用完关闭
        }

        psql = "delete from value_table where vid = ?";
        pstmt = conn.prepareStatement(psql);
        for(i = 0;i < result1.size(); ++i) {
            pstmt.setInt(1, result1.get(i).id);
            pstmt.executeUpdate();
        }
        pstmt.close();

        psql = "delete from data_table where vid = ?";
        pstmt = conn.prepareStatement(psql);
        for(i = 0;i < result1.size(); ++i) {
            pstmt.setInt(1, result1.get(i).id);
            pstmt.executeUpdate();
        }
        pstmt.close();

        /*if(rs != null)
            rs.close();
        if(pstmt != null)
            pstmt.close();*/
        if (conn != null) {
            conn.close();
        }
    }

    /**
     * 更新item_table中的纪录，若对应的iid为0，则插入该条目
     *
     * @param new_item 表中的一行
     */
    public void update_ItemTable(DbItem new_item) throws SQLException {
        Connection conn = getConnection();
        String psql;
        PreparedStatement pstmt;

        if(new_item.iid == 0) {
            psql = "insert into item_table values(null, ?, ?, ?, ?)";
            pstmt = conn.prepareStatement(psql);
            pstmt.setInt(1, new_item.cid);
            pstmt.setString(2, new_item.name);
            pstmt.setInt(3, new_item.time_interval);
            pstmt.setString(4, new_item.mail);
            pstmt.executeUpdate();
            ResultSet rs = pstmt.getGeneratedKeys();
            if (rs.next()) {
                new_item.iid = rs.getInt(1);
            }
            pstmt.close();
        }

        else {
            psql = "update item_table set cid = ?, name = ?, time_interval = ?, mail = ? where iid = ?";
            pstmt = conn.prepareStatement(psql);
            pstmt.setInt(1, new_item.cid);
            pstmt.setString(2, new_item.name);
            pstmt.setInt(3, new_item.time_interval);
            pstmt.setString(4, new_item.mail);
            pstmt.setInt(5, new_item.iid);
            pstmt.executeUpdate();
            pstmt.close();
        }

        if(conn != null)
            conn.close();
    }

    /**
     * 查询graph_table中gid为给定值的纪录
     *
     * @param gid 要查询纪录的cid的值
     * @return 返回相应的行
     */
    public DbGraph query_GraphTable(int gid) throws SQLException {
        Connection conn = getConnection();
        String sql = "select * from graph_table where gid = "+gid;
        ResultSet rs = conn.createStatement().executeQuery(sql);
        try {
            if (rs.next()) {
                return (new DbGraph(rs.getInt("gid"), rs.getInt("iid"), rs.getString("name"), rs.getInt("type")));
            } else {
                return null;
            }
        } finally {
            if(conn != null)
                conn.close();
        }
    }

    /**
     * 获取指定监控项下的所有图表。
     *
     * @param itemID 指定监控项ID。
     * @return 图表列表，若监控项不存在则返回空列表（非null）。
     */
    public Vector<DbGraph> query_GraphTableOfItem(int itemID) throws SQLException {
        Vector<DbGraph> graph = new Vector();
        Connection conn = getConnection();
        String sql = "select * from graph_table where iid = "+itemID;
        ResultSet rs = conn.createStatement().executeQuery(sql);
        try {
            while (rs.next()) {
                graph.add(new DbGraph(rs.getInt("gid"), rs.getInt("iid"), rs.getString("name"),
                                    rs.getInt("type")));
            }
        } finally {
            /*if(rs != null)
                rs.close();*/
            if (conn != null) {
                conn.close();
            }
        }
        return graph;
    }

    /**
     * 查询graph_table中name和8id为给定值的纪录
     *
     * @param name 表中name的值
     * @param iid 表中iid的
     * @return 返回一条或多条记录
     */
    public DbGraph query_GraphTable(String name, int iid) throws SQLException {
        Connection conn = getConnection();
        String psql;
        PreparedStatement pstmt;
        ResultSet rs;

        psql = "select * from graph_table where name = ? && iid = ?";
        pstmt = conn.prepareStatement(psql);
        pstmt.setString(1, name);
        pstmt.setInt(2, iid);
        rs = pstmt.executeQuery();
        try {
            if (rs.next()) {
                return (new DbGraph(rs.getInt("gid"), rs.getInt("iid"), rs.getString("name"), rs.getInt("type")));
            } else {
                return null;
            }
        } finally {
            /*if(rs != null)
                rs.close();
            if(pstmt != null)
                pstmt.close();*/
            if (conn != null) {
                conn.close();
            }
        }
    }

    /**
     * 删除graph_table中与给定gid相等的条目
     *
     * @param gid 要删除条目的gid的值
     */
    public void delete_GraphTable(int gid) throws SQLException {
        Connection conn = getConnection();
        int i;
        String sql;
        ResultSet rs;
        Vector<DbId> result = new Vector();
        String psql = "delete from graph_table where gid = "+gid;
        PreparedStatement pstmt = conn.prepareStatement(psql);
        pstmt.executeUpdate();
        pstmt.close();

        psql = "delete from warning_table where gid = "+gid;
        pstmt = conn.prepareStatement(psql);
        pstmt.executeUpdate();
        pstmt.close();

        sql = "select * from value_table where gid = "+gid;
        rs = conn.createStatement().executeQuery(sql);
        while(rs.next()) {
           result.add(new DbId(rs.getInt("vid")));
        }

        psql = "delete from value_table where vid = ?";
        pstmt = conn.prepareStatement(psql);
        for(i = 0;i < result.size(); ++i) {
            pstmt.setInt(1, result.get(i).id);
            pstmt.executeUpdate();
        }
        pstmt.close();

        psql = "delete from data_table where vid = ?";
        pstmt = conn.prepareStatement(psql);
        for(i = 0;i < result.size(); ++i) {
            pstmt.setInt(1, result.get(i).id);
            pstmt.executeUpdate();
        }
        pstmt.close();

        /*if(rs != null)
            rs.close();
        if(pstmt != null)
            pstmt.close();*/
        if (conn != null) {
            conn.close();
        }
    }

    /**
     * 若给定gid为0，则插入此条目。若gid非0，则更新相应条目
     *
     * @param new_graph 要插入或更新的条目
     */
    public void update_GraphTable(DbGraph new_graph) throws SQLException {
        Connection conn = getConnection();
        String psql;
        PreparedStatement pstmt;

        if(new_graph.gid == 0) {
            psql = "insert into graph_table values(null, ?, ?, ?)";
            pstmt = conn.prepareStatement(psql);
            pstmt.setInt(1, new_graph.iid);
            pstmt.setString(2, new_graph.name);
            pstmt.setInt(3, new_graph.type);
            pstmt.executeUpdate();
            ResultSet rs = pstmt.getGeneratedKeys();
            if (rs.next()) {
                new_graph.gid = rs.getInt(1);
            }
            pstmt.close();
        }

        else {
            psql = "update graph_table set iid = ?, name = ?, type = ? where gid = ?";
            pstmt = conn.prepareStatement(psql);
            pstmt.setInt(1, new_graph.iid);
            pstmt.setString(2, new_graph.name);
            pstmt.setInt(3, new_graph.type);
            pstmt.setInt(4, new_graph.gid);
            pstmt.executeUpdate();
            pstmt.close();

        }

        /*if(pstmt != null)
            pstmt.close();*/
        if (conn != null) {
            conn.close();
        }
    }

    /**
     * 查询value_table中vid为给定值的纪录
     *
     * @param vid 要查询纪录的vid的值
     * @return 返回相应的行
     */
    public DbValue query_ValueTable(int vid) throws SQLException {
        Connection conn = getConnection();
        String sql = "select * from value_table where vid = "+vid;
        ResultSet rs = conn.createStatement().executeQuery(sql);
        try {
            if (rs.next()) {
                return (new DbValue(rs.getInt("vid"), rs.getInt("gid"), rs.getString("name"),
                        rs.getInt("min_num"), rs.getInt("max_num"), rs.getInt("type")));
            } else {
                return null;
            }
        } finally {
            /*if(rs != null)
                rs.close();*/
            if (conn != null) {
                conn.close();
            }
        }
    }

    /**
     * 获取指定图表下的所有监控值。
     *
     * @param graphID 指定图表ID。
     * @return 监控值列表，若图表不存在则返回空列表（非null）。
     */
    public Vector<DbValue> query_ValueTableOfGraph(int graphID) throws SQLException {
        Vector<DbValue> value = new Vector();
        Connection conn = getConnection();
        String sql = "select * from value_table where gid = "+graphID;
        ResultSet rs = conn.createStatement().executeQuery(sql);
        try {
            while (rs.next()) {
                value.add(new DbValue(rs.getInt("vid"), rs.getInt("gid"), rs.getString("name"),
                                    rs.getInt("min_num"), rs.getInt("max_num"), rs.getInt("type")));
            }
        } finally {
            /*if(rs != null)
                rs.close();*/
            if (conn != null) {
                conn.close();
            }
        }
        return value;
    }

    /**
     * 查询value_table中name和gid为给定值的纪录
     *
     * @param name 表中name的值
     * @param gid 表中gid的值
     * @return 返回一条或多条记录
     */
    public DbValue query_ValueTable(String name, int gid) throws SQLException {
        Connection conn = getConnection();
        String psql;
        PreparedStatement pstmt;
        ResultSet rs;

        psql = "select * from value_table where name = ? && gid = ?";
        pstmt = conn.prepareStatement(psql);
        pstmt.setString(1, name);
        pstmt.setInt(2, gid);
        rs = pstmt.executeQuery();
        try {
            if (rs.next()) {
                return (new DbValue(rs.getInt("vid"), rs.getInt("gid"), rs.getString("name"),
                        rs.getInt("min_num"), rs.getInt("max_num"), rs.getInt("type")));
            } else {
                return null;
            }
        } finally {
            /*if(rs != null)
                rs.close();
            if(pstmt != null)
                pstmt.close();*/
            if (conn != null) {
                conn.close();
            }
        }
    }

    /**
     * 删除value_table中与给定vid相等的条目
     *
     * @param vid 要删除条目的vid的值
     */
    public void delete_ValueTable(int vid) throws SQLException {
        Connection conn = getConnection();
        String psql = "delete from value_table where vid = "+vid;
        PreparedStatement pstmt = conn.prepareStatement(psql);
        pstmt.executeUpdate();
        pstmt.close();

        psql = "delete from warning_table where vid = "+vid;
        pstmt = conn.prepareStatement(psql);
        pstmt.executeUpdate();
        pstmt.close();

        psql = "delete from data_table where vid = "+vid;
        pstmt = conn.prepareStatement(psql);
        pstmt.executeUpdate();
        pstmt.close();

        /*if(pstmt != null)
            pstmt.close();*/
        if (conn != null) {
            conn.close();
        }
    }

    /**
     * 若给定vid为0，则插入此条目。若vid非0，则更新相应条目
     *
     * @param new_value 要插入或更新的条目
     */
    public void update_ValueTable(DbValue new_value) throws SQLException {
        Connection conn = getConnection();
        String psql;
        PreparedStatement pstmt;

        if(new_value.vid == 0) {
            psql = "insert into value_table values(null, ?, ?, ?, ?, ?)";
            pstmt = conn.prepareStatement(psql);
            pstmt.setInt(1, new_value.gid);
            pstmt.setString(2, new_value.name);
            pstmt.setInt(3, new_value.min_num);
            pstmt.setInt(4, new_value.max_num);
            pstmt.setInt(5, new_value.type);
            pstmt.executeUpdate();
            ResultSet rs = pstmt.getGeneratedKeys();
            if (rs.next()) {
                new_value.vid = rs.getInt(1);
            }
            pstmt.close();
        }

        else {
            psql = "update value_table set gid = ?, name = ?, min_num = ?, max_num = ?, type = ? where vid = ?";
            pstmt = conn.prepareStatement(psql);
            pstmt.setInt(1, new_value.gid);
            pstmt.setString(2, new_value.name);
            pstmt.setInt(3, new_value.min_num);
            pstmt.setInt(4, new_value.max_num);
            pstmt.setInt(5, new_value.type);
            pstmt.setInt(6, new_value.vid);
            pstmt.executeUpdate();
            pstmt.close();

        }

        /*if(pstmt != null)
            pstmt.close();*/
        if (conn != null) {
            conn.close();
        }
    }

    /**
     * 查询data_table中rid为给定值的纪录
     *
     * @param rid 要查询纪录的rid的值
     * @return 返回相应的行
     */
    public DbData query_DataTable(int rid) throws SQLException {
        Connection conn = getConnection();
        String sql = "select * from data_table where rid = "+rid;
        ResultSet rs = conn.createStatement().executeQuery(sql);
        try {
            if (rs.next()) {
                return (new DbData(rs.getInt("rid"), rs.getInt("vid"), rs.getInt("value"),
                        convertTimestamp(rs.getTimestamp("time")), rs.getInt("layer")));
            } else {
                return null;
            }
        } finally {
            /*if(rs != null)
                rs.close();*/
            if (conn != null) {
                conn.close();
            }
        }
    }

    /**
     * 查询data_table中vid为给定值且time在给定时间段内的纪录
     *
     * @param vid 表中vid的值
     * @param low_time 时间下线
     * @param high_time 时间上线
     * @return 返回一条或多条记录
     */
    public Vector<DbData> query_DataTable(int vid, Date low_time, Date high_time) throws SQLException {
        Connection conn = getConnection();
        String psql;
        PreparedStatement pstmt;
        ResultSet rs;
        Vector<DbData> data = new Vector();

        psql = "select * from data_table where vid = ? && time >= ? && time <= ? order by time";
        pstmt = conn.prepareStatement(psql);
        pstmt.setInt(1, vid);
        pstmt.setTimestamp(2, convertTimestamp(low_time));
        pstmt.setTimestamp(3, convertTimestamp(high_time));
        rs = pstmt.executeQuery();
        while(rs.next()) {
            data.add(new DbData(rs.getInt("rid"), rs.getInt("vid"), rs.getInt("value"),
                    convertDate(rs.getTimestamp("time")), rs.getInt("layer")));
        }
        /*if(rs != null)
            rs.close();
        if(pstmt != null)
            pstmt.close();*/
        if (conn != null) {
            conn.close();
        }
        return data;
    }

    /**
     * 查询data_table中vid和layer为给定值且time在给定时间段内的纪录
     *
     * @param vid 表中vid的值
     * @param low_time 时间下线
     * @param high_time 时间上线
     * @param layer 纪录的层次
     * @return 返回一条或多条记录
     */
    public Vector<DbData> query_DataTable(int vid, Date low_time, Date high_time, int low_layer, int high_layer) throws SQLException {
        Connection conn = getConnection();
        String psql;
        PreparedStatement pstmt;
        ResultSet rs;
        Vector<DbData> data = new Vector();

        psql = "select * from data_table where vid = ? && time >= ? && time <= ? && layer >= ? && layer <= ? order by time";
        pstmt = conn.prepareStatement(psql);
        pstmt.setInt(1, vid);
        pstmt.setTimestamp(2, convertTimestamp(low_time));
        pstmt.setTimestamp(3, convertTimestamp(high_time));
        pstmt.setInt(4, low_layer);
        pstmt.setInt(5, high_layer);
        rs = pstmt.executeQuery();
        while(rs.next()) {
            data.add(new DbData(rs.getInt("rid"), rs.getInt("vid"), rs.getInt("value"),
                    convertTimestamp(rs.getTimestamp("time")), rs.getInt("layer")));
        }
        /*if(rs != null)
            rs.close();
        if(pstmt != null)
            pstmt.close();*/
        if (conn != null) {
            conn.close();
        }
        return data;
    }

    /**
     * 删除data_table中与给定rid相等的条目
     *
     * @param rid 要删除条目的rid的值
     */
    public void delete_DataTable(int rid) throws SQLException {
        Connection conn = getConnection();
        String psql = "delete from data_table where rid = "+rid;
        PreparedStatement pstmt = conn.prepareStatement(psql);
        pstmt.executeUpdate();
        pstmt.close();

        psql = "delete from warning_table where rid = "+rid;
        pstmt = conn.prepareStatement(psql);
        pstmt.executeUpdate();
        pstmt.close();

        /*if(pstmt != null)
            pstmt.close();*/
        if (conn != null) {
            conn.close();
        }
    }

    /**
     * 若给定rid为0，则插入此条目。若rid非0，则更新相应条目
     *
     * @param new_data 要插入或更新的条目
     */
    public void update_DataTable(DbData new_data) throws SQLException {
        Connection conn = getConnection();
        String psql;
        PreparedStatement pstmt;

        if(new_data.rid == 0) {
            psql = "insert into data_table values(null, ?, ?, ?, ?)";
            pstmt = conn.prepareStatement(psql);
            pstmt.setInt(1, new_data.vid);
            pstmt.setInt(2, new_data.value);
            pstmt.setTimestamp(3, convertTimestamp(new_data.time));
            pstmt.setInt(4, new_data.layer);
            pstmt.executeUpdate();
            ResultSet rs = pstmt.getGeneratedKeys();
            if (rs.next()) {
                new_data.rid = rs.getInt(1);
            }
            pstmt.close();
        }

        else {
            psql = "update data_table set vid = ?, value = ?, time = ?, layer = ? where rid = ?";
            pstmt = conn.prepareStatement(psql);
            pstmt.setInt(1, new_data.vid);
            pstmt.setInt(2, new_data.value);
            pstmt.setTimestamp(3, convertTimestamp(new_data.time));
            pstmt.setInt(4, new_data.layer);
            pstmt.setInt(5, new_data.rid);
            pstmt.executeUpdate();
            pstmt.close();
        }

        /*if(pstmt != null)
            pstmt.close();*/
        if (conn != null) {
            conn.close();
        }
    }

    /**
     * 返回所有用户的列表。
     *
     * @return 所有用户列表。
     */
    public Vector<DbUser> query_UserTable_all() throws SQLException {
        Vector<DbUser> dbuser = new Vector();
        Connection conn = getConnection();
        String sql = "select * from user_table";
        ResultSet rs = conn.createStatement().executeQuery(sql);
        try {
            while (rs.next()) {
                dbuser.add(new DbUser(rs.getInt("uid"), rs.getString("name"), rs.getString("password")));
            }
        } finally {
            /*if(rs != null)
                rs.close();*/
            if (conn != null) {
                conn.close();
            }
        }
        return dbuser;
    }

    /**
     * 查询user_table中uid为给定值的纪录
     *
     * @param uid 要查询纪录的uid的值
     * @return 返回相应的行
     */
    public DbUser query_UserTable(int uid) throws SQLException {
        Connection conn = getConnection();
        String sql = "select * from user_table where uid = "+uid;
        ResultSet rs = conn.createStatement().executeQuery(sql);
        try {
            if (rs.next()) {
                return (new DbUser(rs.getInt("uid"), rs.getString("name"), rs.getString("password")));
            } else {
                return null;
            }
        } finally {
            /*if(rs != null)
                rs.close();*/
            if (conn != null) {
                conn.close();
            }
        }
    }

    /**
     * 获取指定用户名的用户。
     *
     * @param username 用户名。
     * @return 用户对象，若不存在则为null。
     */
    public DbUser query_UserTable(String username) throws SQLException {
        Connection conn = getConnection();
        String sql = "select * from user_table where name = '"+username+"'";
        ResultSet rs = conn.createStatement().executeQuery(sql);
        try {
            if (rs.next()) {
                return (new DbUser(rs.getInt("uid"), rs.getString("name"), rs.getString("password")));
            } else {
                return null;
            }
        } finally {
            /*if(rs != null)
                rs.close();*/
            if (conn != null) {
                conn.close();
            }
        }
    }

    /**
     * 删除user_table中与给定uid相等的条目
     *
     * @param uid 要删除条目的uid的值
     */
    public void delete_UserTable(int uid) throws SQLException {
        Connection conn = getConnection();
        String psql = "delete from user_table where uid = "+uid;
        PreparedStatement pstmt = conn.prepareStatement(psql);
        pstmt.executeUpdate();
        pstmt.close();

        /*if(pstmt != null)
            pstmt.close();*/
        if (conn != null) {
            conn.close();
        }
    }

    /**
     * 若给定uid为0，则插入此条目。若uid非0，则更新相应条目
     *
     * @param new_user 要插入或更新的条目
     */
    public void update_UserTable(DbUser new_user) throws SQLException {
        Connection conn = getConnection();
        String psql;
        PreparedStatement pstmt;

        if(new_user.uid == 0) {
            psql = "insert into user_table values(null, ?, ?)";
            pstmt = conn.prepareStatement(psql);
            pstmt.setString(1, new_user.name);
            pstmt.setString(2, new_user.password);
            pstmt.executeUpdate();
            ResultSet rs = pstmt.getGeneratedKeys();
            if (rs.next()) {
                new_user.uid = rs.getInt(1);
            }
            pstmt.close();
        }

        else {
            psql = "update user_table set name = ?, password = ? where uid = ?";
            pstmt = conn.prepareStatement(psql);
            pstmt.setString(1, new_user.name);
            pstmt.setString(2, new_user.password);
            pstmt.setInt(3, new_user.uid);
            pstmt.executeUpdate();
            pstmt.close();
        }

        /*if(pstmt != null)
            pstmt.close();*/
        if (conn != null) {
            conn.close();
        }
    }

    /**
     * 根据警告项所属记录ID获取警告项。
     *
     * @return
     */
    public DbWarning query_WarningTable(int wid) throws SQLException {
        Connection conn = getConnection();
        String sql = "select * from warning_table where wid = "+wid;
        ResultSet rs = conn.createStatement().executeQuery(sql);
        try {
            if (rs.next()) {
                return (new DbWarning(rs.getInt("wid"), rs.getInt("rid"), rs.getInt("vid"), rs.getInt("gid"),
                                      rs.getInt("iid"), rs.getInt("cid"), rs.getString("message"), convertTimestamp(rs.getTimestamp("time"))));
            } else {
                return null;
            }
        } finally {
            /*if(rs != null)
                rs.close();*/
            if (conn != null) {
                conn.close();
            }
        }
    }

    /**
     * 根据所属的客户端ID、监控项ID、图表ID、监控值ID和记录ID来获取相应的警告项。若
     * 这些ID中的一个和多个设为0则忽略此ID的匹配条件。
     *
     * @param cid
     * @param iid
     * @param gid
     * @param vid
     * @return
     */
    public Vector<DbWarning> query_WarningTable(int cid, int iid, int gid, int vid) throws SQLException {
        Connection conn = getConnection();
        String psql;
        int flag = 0;
        PreparedStatement pstmt;
        ResultSet rs;
        Vector<DbWarning> warning = new Vector();

        if(cid==0 && iid==0 && gid==0 && vid==0)
            psql = "select * from warning_table";
        else {
            psql = "select * from warning_table where ";
            if(cid != 0) {
                psql = psql+ "cid="+cid;
                flag = 1;
            }
            if(iid != 0){
                if(flag ==0) {
                    psql = psql+ "iid="+iid;
                    flag = 1;
                }
                else {
                    psql = psql+ "&&iid="+iid;
                    flag = 1;
                }
            }
            if(gid != 0) {
                if(flag ==0) {
                    psql = psql+ "gid="+gid;
                    flag = 1;
                }
                else {
                    psql = psql+ "&&gid="+gid;
                    flag = 1;
                }
            }
            if(vid != 0) {
                if(flag ==0) {
                    psql = psql+ "vid="+vid;
                    flag = 1;
                }
                else {
                    psql = psql+ "&&vid="+vid;
                    flag = 1;
                }
            }
        }
        pstmt = conn.prepareStatement(psql + " order by time desc");

        rs = pstmt.executeQuery();
        try {
            while (rs.next()) {
                warning.add(new DbWarning(rs.getInt("wid"), rs.getInt("rid"), rs.getInt("vid"), rs.getInt("gid"),
                                      rs.getInt("iid"), rs.getInt("cid"), rs.getString("message"), convertTimestamp(rs.getTimestamp("time"))));
            }
        } finally {
            /*if(rs != null)
                rs.close();*/
            if (conn != null) {
                conn.close();
            }
        }
        return warning;
    }

    /**
     * 删除warning_table中与给定rid相等的条目
     *
     * @param wid 要删除条目的rid的值
     */
    public void delete_WarningTable(int wid) throws SQLException {
        Connection conn = getConnection();
        String psql = "delete from warning_table where wid = "+wid;
        PreparedStatement pstmt = conn.prepareStatement(psql);
        pstmt.executeUpdate();
        pstmt.close();

        /*if(pstmt != null)
            pstmt.close();*/
        if (conn != null) {
            conn.close();
        }
    }

    /**
     * 若给定wid为0，则插入此条目。若wid非0，则更新相应条目
     *
     * @param new_warning 要插入或更新的条目
     */
    public void update_WarningTable(DbWarning new_warning) throws SQLException {
        Connection conn = getConnection();
        String psql;
        PreparedStatement pstmt;

        if(new_warning.wid == 0) {
            psql = "insert into warning_table values(null, ?, ?, ?, ?, ?, ?, ?)";
            pstmt = conn.prepareStatement(psql);
            pstmt.setInt(1, new_warning.rid);
            pstmt.setInt(2, new_warning.vid);
            pstmt.setInt(3, new_warning.gid);
            pstmt.setInt(4, new_warning.iid);
            pstmt.setInt(5, new_warning.cid);
            pstmt.setString(6, new_warning.message);
            pstmt.setTimestamp(7, convertTimestamp(new_warning.time));
            pstmt.executeUpdate();
            ResultSet rs = pstmt.getGeneratedKeys();
            if (rs.next()) {
                new_warning.wid = rs.getInt(1);
            }
            pstmt.close();
        }

        else {
            psql = "update warning_table set rid = ?, vid = ?, gid = ?, iid = ?, cid = ?, message = ?, time = ? where wid = ?";
            pstmt = conn.prepareStatement(psql);
            pstmt.setInt(1, new_warning.rid);
            pstmt.setInt(2, new_warning.vid);
            pstmt.setInt(3, new_warning.gid);
            pstmt.setInt(4, new_warning.iid);
            pstmt.setInt(5, new_warning.cid);
            pstmt.setString(6, new_warning.message);
            pstmt.setTimestamp(7, convertTimestamp(new_warning.time));
            pstmt.setInt(8, new_warning.wid);
            pstmt.executeUpdate();
            pstmt.close();

        }

        /*if(pstmt != null)
            pstmt.close();*/
        if (conn != null) {
            conn.close();
        }
    }

    /**
     * 计算data_table表中最近时间内相应纪录的平均值并把结果插入到表中
     *
     * @param time 时间长度
     * @param iid 监控项的值
     */
    public void hebing() throws SQLException {

    }

    
    public static java.sql.Timestamp convertTimestamp(java.sql.Date date) {
        return new java.sql.Timestamp(date.getTime());
    }

    public static java.sql.Date convertTimestamp(java.sql.Timestamp time) {
        return new java.sql.Date(time.getTime());
    }

    /**
     * 将通用的java.util.Date转换成java.sql.Date。
     *
     * @param date 要转换的日期时间对象。
     * @return 相应的java.sql.Date对象。
     */
    public static java.sql.Date convertDate(java.util.Date date) {
        return new java.sql.Date(date.getTime());
    }

    /**
     * 反向转换时间类型。
     *
     * @param date 要转换的日期时间对象。
     * @return 相应的java.util.Date对象。
     */
    public static java.util.Date convertDate(java.sql.Date date) {
        return new java.util.Date(date.getTime());
    }
}
